<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2019/1/15 0015
 * Time: 13:32
 */

namespace timer_job\common\model;

use think\Exception;

/**
 *
 * 为了解决多数据库连接和连接重用
 * 以及多进程里的断链问题而封装
 * 的基础数据库操作类
 *
 * 实例化后，在这个对象里的操作
 * 的连接都是重用的，除非调用close（）方法
 * 注意这里用类里的closeLink（）释放
 *
 * 创建对象就会新建连接，重用连接是针对单个对象的
 *
 *
 * 此类里实现的查询select 更新update、updateAll等等
 * 方法都有统一的返回规范，返回false时代表执行失败，
 * 执行成功返回true或者查询结果
 *
 * Class OriginalMysql
 * @package job\common\model
 */
class KeepLinkMysql {
    /*初始化数据*/
    private $_hostname = 'rm-bp1v0s58si8vr4g087o.mysql.rds.aliyuncs.com';
    private $_username = 'deng2019';
    private $_password = '#1$va%hwzz^89&*(0)';
    private $_charset = 'utf8mb4';
    private $_database = 'job_timer_tick';
    private $_conn;

    protected $connection;
    protected $table_name;

    public $error_msg = '';

    /*构造函数*/
    public function  __construct(){
        if (!$this->table_name) {
            throw new Exception('请设置对象table_name属性');
        }

        if ($this->connection) {
            $this->_hostname = $this->connection['hostname'];
            $this->_username = $this->connection['username'];
            $this->_password = $this->connection['password'];
            $this->_charset = $this->connection['charset'];
            $this->_database = $this->connection['database'];
        }
        $this->connect();
    }

    /*连接mysql*/
    // 连接数据库
    private  function connect() {
        if (!$this->_conn = new \mysqli($this->_hostname, $this->_username, $this->_password, $this->_database, 3306)) {
            throw new Exception('连接数据库失败：' . mysqli_connect_error());
        }
        if (!$this->_conn->set_charset($this->_charset)) {
            throw new Exception('数据库设置字符集失败：' . $this->_conn->error);
        }
    }

    /**
     * 单条sql执行，并排除数据库断链的
     * 保证连接畅通
     *
     * 通过该函数执行的sql如果返回为false那么
     * 执行失败，可以调用该对象的error_msg打印出错误
     *
     * @param $sql
     * @return bool|object mysqli_result
     * @throws \think\Exception
     */
    public function safeQuery($sql) {
        try{
            if ($this->_conn) {
                $res = $this->_conn->query($sql);
            } else {
                $this->connect();
                $res = $this->_conn->query($sql);
            }
            if ($res === false) {
                $this->error_msg = $this->_conn->error;
            }
            return $res;
        } catch(\Exception $e) {
            if (in_array(mysqli_connect_errno(), [2006, 2013])) {
                $this->connect();
                $res = $this->_conn->query($sql);
                if ($res === false) {
                    $this->error_msg = $this->_conn->error;
                }
                return $res;
            } else {
                throw new Exception($e->getMessage());
            }
        }
    }

    /**
     * 返回查询结果
     *
     * @param $sql
     * @param int $type
     * @return false|array
     * @throws Exception
     */
    public function select($sql, $type = MYSQLI_ASSOC) {
        $res = $this->safeQuery($sql);
        if ($res) {
            $result = $res->fetch_all($type);
            $res->free_result();
            return $result;
        }
        return $res;
    }

    public function one($sql) {
        $res = $this->safeQuery($sql);
        if ($res) {
            $result = $res->fetch_array(MYSQLI_ASSOC);
            $res->free_result();
            return $result;
        }
        return $res;
    }

    /**
     * 返回
     *
     * @return mixed
     */
    public function getConn() {
        return $this->_conn;
    }

    /**
     * 批量新增
     */
    public function addAll($data) {
        $current = current($data);
        $fields = array_keys($current);
        $fields_str = '';
        foreach ($fields as $f) {
            $fields_str .= " `$f`" . ',';
        }
        $fields_str = rtrim($fields_str, ',');
        $values = [];
        foreach ($data as $item) {
            $val_str = '';
            foreach ($item as $field => $val) {
                $val_str .= " '$val'" . ',';
            }
            $val_str = rtrim($val_str, ',');
            $values[] = $val_str = '(' . $val_str . ')';
        }
        $values = implode(',', $values);
        $sql = "INSERT INTO $this->table_name ($fields_str) VALUES $values";
        return $this->safeQuery($sql);
    }

    /**
     * 单一逻辑or或and的单表更新操作
     *
     * @param $where
     * @param $data
     * @return bool
     * @throws Exception
     */
    public function update($where, $data) {
        $logic = 'AND';
        if (isset($where['logic']) && in_array($where['logic'], ['or', 'OR'])) {
            $logic = $where['logic'];
        }
        $where_str = $this->dealSingleWhere($where, $logic);
        $up_data = '';
        $count = count($data);
        $i = 0;
        foreach ($data as $key => $value) {
            if ($i == $count-1) {
                $up_data .= " `$key` = '$value'";
            } else {
                $up_data .= " `$key` = '$value',";
            }
            $i++;
        }
        $sql = "UPDATE $this->table_name SET $up_data WHERE $where_str";
        return $this->safeQuery($sql);
    }

    /**
     * 单一逻辑or或and的单表删除操作
     *
     * @param $where
     * @return mixed
     * @throws Exception
     */
    public function delete($where) {
        $logic = 'AND';
        if (isset($where['logic']) && in_array($where['logic'], ['or', 'OR'])) {
            $logic = $where['logic'];
        }
        $where_str = $this->dealSingleWhere($where, $logic);
        $sql = "DELETE FROM {$this->table_name} WHERE $where_str";
        return $res = $this->safeQuery($sql);
    }

    /**
     * 处理单一逻辑的where数组
     *
     * @param $where
     * @param string $logic
     * @return string
     * @throws Exception
     */
    private function dealSingleWhere($where, $logic = 'AND') {
        if (is_string($where) || is_numeric($where)) { // 根据主键删除
            $pk = $this->getPrimary();
            $where_str = "`$pk`= $where";
        } else if (is_array($where)) { // 根据where条件删除
            $i = 0;
            $count = count($where);
            $where_str = '';
            foreach ($where as $key => $value) {
                if ($i == $count-1) {
                    $where_str .= " `$key` = $value";
                } else {
                    $where_str .= " `$key` = $value $logic";
                }
                $i++;
            }
        } else {
            throw new Exception('the param where error');
        }
        return $where_str;
    }

    // 手动关闭该连接
    public function closeLink() {
        $this->_conn->close();
        $this->_conn = null;
    }

    /**
     * 主键
     *
     * @return mixed
     */
    public function getPrimary() {
        $res = $this->_conn->query("DESCRIBE " . $this->table_name);
        while( $row = $res->fetch_array()){
            if($row['Key']=='PRI'){
                return  $row['Field'];
                break;
            }
        }
    }

    /**
     * 新增一条数据
     *
     * @param array $data
     * @return bool|object
     * @throws Exception
     */
    public function add(array $data)
    {
        $field_str = '';
        $value_str = '';
        $count = count($data);
        $i = 0;
        foreach ($data as $key => $value) {
            if ($i == $count-1) {
                $field_str .= " `$key`";
                $value_str .= " '{$value}'";
            } else {
                $field_str .= " `$key`,";
                $value_str .= " '{$value}',";
            }
            $i++;
        }
        $sql = "INSERT INTO `$this->table_name`($field_str) VALUES ($value_str)";
        return $this->safeQuery($sql);
    }

    /**
     * 批量更新
     *
     * @param array $data 必须包含主键
     * @return bool
     * @throws Exception
     */
    public function updateAll(array $data){
        $pk = $this->getPrimary();
        $table = $this->table_name;
        if(empty($data)){
            throw new \Exception('params data not exist');
        };
        //获取所有的id
        foreach ($data as $item) {
            $keys[] = $item[$pk];
        }
        $col = $this->safeQuery("SHOW COLUMNS FROM `{$table}`");
        $col_res = $col->fetch_all();
        $col->free_result();
        $cols = [];
        array_map(function($value) use(&$cols){
            $cols[] = $value[0];
        },$col_res);
        //拼接批量更新sql语句
        $sql = "UPDATE {$table} SET ";
        //合成sql语句
        foreach ($data[0] as $key => $value) {
            if(!in_array($key,$cols)) continue;
            if ($key == $pk) continue;
            $sql .= "`{$key}` = CASE `{$pk}` ";
            $i = 0;
            foreach ($data as $k=>$v) {
                $sql .= sprintf("WHEN %d THEN '%s' ", $keys[$i], $v[$key]);
                $i++;
            }
            $sql .= "END, ";
        }
        //把最后一个,去掉
        $sql = substr($sql, 0, strrpos($sql,','));
        //合并所有id
        $ids = implode(',', $keys);
        //拼接sql
        $sql .= " WHERE `{$pk}` IN ({$ids})";
        $res = $this->safeQuery($sql);
        return $res; // 返回的是bool
    }

}